package com.hxut.dao;

import com.hxut.model.User;
import com.hxut.utils.C3p0Utils;
import com.hxut.utils.JdbcUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class UserDao {
    Connection connection;
    PreparedStatement preparedStatement;
    ResultSet resultSet;

    /*public User findUserByNameAndPs(String username,String password){
        try {
            connection = JdbcUtils.getConnnect();
            String sql = "select * from user where username=? and password=? ";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,username);
            preparedStatement.setString(2,password);
            resultSet = preparedStatement.executeQuery();
            User user = new User();
            while (resultSet.next()){
                user.setId(resultSet.getInt("id"));
                user.setUsername(resultSet.getString("username"));
                user.setPassword(resultSet.getString("password"));
                user.setName(resultSet.getString("name"));
                user.setPhone(resultSet.getString("phone"));
                user.setAddress(resultSet.getString("address"));
            }return user;

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(resultSet,preparedStatement,connection);
        }
        return null;

    }*/
    public User findUserByNameAndPs(String username,String password){
            QueryRunner queryRunner = new QueryRunner(C3p0Utils.getDataSource());
            String sql = "select * from user where username=? and password=? ";
            Object[] params = new Object[]{username,password};
        try {
            User user = new User();
            user = queryRunner.query(sql, new BeanHandler<User>(User.class),params);
            return user;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }


    public User findUserByUsername(String username){
        try {
            connection = JdbcUtils.getConnect();
            String sql = "select * from user where username=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,username);
            resultSet = preparedStatement.executeQuery();
            User user = new User();
            while (resultSet.next()){
                user.setId(resultSet.getInt("id"));
                user.setUsername(resultSet.getString("username"));
                user.setPassword(resultSet.getString("password"));
                user.setName(resultSet.getString("name"));
                user.setPhone(resultSet.getString("phone"));
                user.setAddress(resultSet.getString("address"));
            }return user;

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(resultSet,preparedStatement,connection);
        }
        return null;

    }

    public int addUser(User user){
        try {
            connection = JdbcUtils.getConnect();
            String sql = "insert into user (username,email,password,name,phone,address) values(?,?,?,?,?,?)";
            preparedStatement= connection.prepareStatement(sql);
            preparedStatement.setString(1, user.getUsername());
            preparedStatement.setString(2, user.getEmail());
            preparedStatement.setString(3, user.getPassword());
            preparedStatement.setString(4, user.getName());
            preparedStatement.setString(5, user.getPhone());
            preparedStatement.setString(6, user.getAddress());
            int i = preparedStatement.executeUpdate();

            return i;

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(preparedStatement,connection);
        }
        return 0;
    }

    public int updateUser(User user){
        try {
            connection = JdbcUtils.getConnect();
            String sql = "update user set name=?,phone=?,address=? where id =?";
            preparedStatement.setString(1, user.getName());
            preparedStatement.setString(2, user.getPhone());
            preparedStatement.setString(3, user.getAddress());
            preparedStatement.setInt(4, user.getId());
            connection.prepareStatement(sql);
            int i = preparedStatement.executeUpdate();
            return i;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(preparedStatement,connection);
        }
        return 0;
    }

    public int deleteUser(String username){
        QueryRunner queryRunner = new QueryRunner(C3p0Utils.getDataSource());
        String sql = "delete from user where username=?";
        int rows = 0;
        try {
            rows = queryRunner.update(sql, username);
            return  rows;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return  0;
    }
}
